use crate::base;
use crate::common::cs;
use crate::common::models::{Sleet, Weather, Soil};
use sqlx::postgres::PgQueryResult;

/// 查询设备id
pub async fn select_device_id(port: &str, address: u32) -> cs::Result<i32>
{
    let i: (i32, ) = sqlx::query_as(
        &format!(
            "select device_id \
            from {}.gardens_device \
            where device_port = '{}' \
            and device_address = {}",
            base::database_relation(),
            port,
            address
        )
    )
        .fetch_one(base::db_conn_pool())
        .await?;
    Ok(i.0)
}

/// 查询设备id
pub async fn select_device_info(device_id: i32) -> cs::Result<(String, i32, i32)>
{
    let i: (String, i32, i32) = sqlx::query_as(
        &format!(
            "select device_port,device_address,device_type \
            from {}.gardens_device \
            where device_id = {}",
            base::database_relation(),
            device_id
        )
    )
        .fetch_one(base::db_conn_pool())
        .await?;
    Ok(i)
}

/// 查询所有设备
pub async fn select_all_device() -> cs::Result<Vec<(i32, String, i32, i32)>>
{
    let v: Vec<(i32, String, i32, i32)> = sqlx::query_as(
        &format!(
            "select device_id,device_port,device_address,device_type \
            from {}.gardens_device",
            base::database_relation()
        )
    )
        .fetch_all(base::db_conn_pool())
        .await?;
    Ok(v)
}

/// 根据键删除设备
pub async fn delete_device_by_id(device_id: i32) -> cs::Result<PgQueryResult>
{
    let r = sqlx::query(
        &format!(
            "delete from {}.gardens_device where device_id = {}",
            base::database_relation(), device_id
        )
    )
        .execute(base::db_conn_pool())
        .await?;
    Ok(r)
}

/// 删除所有设备
pub async fn delete_all_device() -> cs::Result<PgQueryResult>
{
    let r = sqlx::query(
        &format!(
            "delete from {}.gardens_device where true",
            base::database_relation()
        )
    )
        .execute(base::db_conn_pool())
        .await?;
    Ok(r)
}

/// 新增设备
pub async fn insert_device(device_type: u32, serial_port: &str, device_address: u32) -> cs::Result<i32>
{
    let result: (i32, ) = sqlx::query_as(&format!("\
    insert into {}.gardens_device \
    (device_id, device_type, device_port, device_address) \
    values (default, {}, '{}', {}) \
    returning device_id", base::database_relation(), device_type, serial_port, device_address))
        .fetch_one(base::db_conn_pool()).await?;
    Ok(result.0)
}

/// 插入雨雪数据
pub async fn insert_sleet(device_id: i32, sleet: &Sleet) -> cs::Result<PgQueryResult> {
    let result = sqlx::query(&format!("\
        insert into {}.gardens_sleet \
        (sleet_id, device_id, sleet_status, sleet_time) \
        values (default, {}, {}, now());", base::database_relation(), device_id, sleet.status))
        .execute(base::db_conn_pool()).await?;
    Ok(result)
}

/// 插入雨雪数据
pub async fn insert_weather(device_id: i32, weather: &Weather) -> cs::Result<PgQueryResult> {
    let result = sqlx::query(&format!("\
        insert into {}.gardens_weather \
        (weather_id, device_id, weather_humidity, weather_temperature,\
         weather_noise, weather_pm25, weather_pm10, weather_time) \
        values (default, {}, {}, {}, {}, {}, {}, now());", base::database_relation(),
                                      device_id,
                                      weather.humidity,
                                      weather.temperature,
                                      weather.noise,
                                      weather.pm25,
                                      weather.pm10
    ))
        .execute(base::db_conn_pool()).await?;
    Ok(result)
}

/// 插入土壤数据
pub async fn insert_soil(device_id: i32, soil: &Soil) -> cs::Result<PgQueryResult> {
    let result = sqlx::query(&format!("
    insert into {}.gardens_soil \
    (soil_id, device_id,\
    soil_moisture, soil_temperature, soil_electrical, soil_nitrogen, soil_phosphorus, soil_potassium,\
    soil_salinity, soil_tds, soil_time) \
    values (default, {}, {}, {}, {}, {}, {}, {}, {}, {}, now())", base::database_relation(),
                                      device_id,
                                      soil.moisture,
                                      soil.temperature,
                                      soil.electrical,
                                      soil.nitrogen,
                                      soil.phosphorus,
                                      soil.potassium,
                                      soil.salinity,
                                      soil.tds
    ))
        .execute(base::db_conn_pool()).await?;
    Ok(result)
}

/// 插入操作记录
pub async fn insert_operation(device_id: i32, operation: i32) -> cs::Result<PgQueryResult> {
    let result = sqlx::query(&format!("
    insert into {}.gardens_operation_log (device_id, operation_log_operation, operation_log_time)
    values ({}, {}, now());", base::database_relation(), device_id, operation
    ))
        .execute(base::db_conn_pool()).await?;
    Ok(result)
}

/// 插入开关状态
pub async fn insert_switch(device_id: i32, status: bool) -> cs::Result<PgQueryResult> {
    let result = sqlx::query(&format!("insert into {}.gardens_switch\
     (device_id, switch_open, switch_time) VALUES \
     ({}, {}, now());", base::database_relation(), device_id, status))
        .execute(base::db_conn_pool()).await?;
    Ok(result)
}

/// 插入集成开关状态
pub async fn insert_paralleling(device_id: i32, open1: bool, open2: bool, open3: bool) -> cs::Result<PgQueryResult> {
    let result = sqlx::query(&format!("insert into {}.gardens_paralleling \
    (device_id, paralleling_open1, paralleling_open2, paralleling_open3,paralleling_time) \
    VALUES ({}, {}, {}, {}, now());", base::database_relation(), device_id, open1, open2, open3))
        .execute(base::db_conn_pool()).await?;
    Ok(result)
}

pub async fn truncate_device() -> cs::Result<PgQueryResult> {
    let result = sqlx::query(
        &format!("truncate table {}.gardens_device;", base::database_relation())
    ).execute(base::db_conn_pool()).await?;
    Ok(result)
}

#[cfg(test)]
mod tests {
    use crate::database::dao;
    use crate::common::models::Sleet;

    #[tokio::test]
    async fn test() {
        let a = dao::insert_device(1, "COM2", 1).await;
        println!("{:?}", a);
        let a = dao::select_device_id("COM2", 1).await;
        println!("{:?}", a);
    }

    #[tokio::test]
    async fn test2() {
        let mut sleet = Sleet::default();
        sleet.status = true;
        sleet.address = 713;
        println!("{:?}", dao::insert_sleet(99, &sleet).await);
    }

    #[tokio::test]
    async fn test3() {
        println!("{:?}", dao::insert_operation(1, 2).await);
    }

    #[tokio::test]
    async fn test4() {
        let v = dao::select_all_device().await.unwrap();
        println!("{:?}", v);
    }
}